package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import ModelLayer.Supplier;

public class DbSupplier implements IfDbSupplier
{
private Connection con;
	
	public DbSupplier()
	{
		con = DbConnection.getInstance().getDBcon();
	}

	public ArrayList<Supplier> getAllSupplier(boolean retriveAssociation) 
	{
        return miscWhere("", retriveAssociation);
	}

	private ArrayList<Supplier> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Supplier> list = new ArrayList<Supplier>();	
		
	    String query =  buildQuery(wClause);
  
            try
            { 
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() )
		{
	     	 Supplier supplierObject = new Supplier();
		 supplierObject = buildSupplier(results);	
                 list.add(supplierObject);	
		}
                 stmt.close();     
		}
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}

	private Supplier singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Supplier supplierObject = new Supplier();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try
		{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() )
	 		{
                supplierObject = buildSupplier(results);
                stmt.close();
			}
            else
            {
                supplierObject = null;
            }
		}	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return supplierObject;
	}
	
	private String buildQuery(String wClause)
	{
	    String query="SELECT supplierid, name, address, zipCode, phoneNr, country, email FROM supplier";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}

	private Supplier buildSupplier(ResultSet results)
      {   
		Supplier supplierObject = new Supplier();
          try
          {
        	    supplierObject.setSupplierID(results.getInt("supplierid"));
                supplierObject.setName(results.getString("name"));
                supplierObject.setAddress(results.getString("address"));
                supplierObject.setZipCode(results.getString("zipCode"));
                supplierObject.setPhoneNr(results.getString("phoneNr"));
                supplierObject.setCountry(results.getString("country"));
                supplierObject.setEmail(results.getString("email"));
          }
         catch(Exception e)
         {
             System.out.println("There is a failure in building the Supplier");
         }
         return supplierObject;
      }
	
	public Supplier findSupplierByName(String name, boolean retriveAssociation) 
	{
		String wClause = "name = '" + name + "'";
        System.out.println("SearchSupplier " + wClause);
        return singleWhere(wClause, retriveAssociation);
	}
	
	public Supplier findSupplierByID(int id, boolean retriveAssociation) 
	{
		String wClause = "SupplierID = '" + id + "'";
        System.out.println("SearchSupplier " + wClause);
        return singleWhere(wClause, retriveAssociation);
	}

	public int insertSupplier(Supplier Supplier) throws Exception 
	{
		int lol = -1;
		String query="INSERT INTO supplier(name, address, zipcode, phonenr, country, email) VALUES('"+
		  		Supplier.getName()  + "','"  +
			    Supplier.getAddress()  + "','"  +
		  		Supplier.getZipCode() + "','" +
			    Supplier.getPhoneNr() + "','" +
		  		Supplier.getCountry() +  "','" +
  				Supplier.getEmail() +  "')";
		try
		{ 
	          Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  lol = stmt.executeUpdate(query);
	          stmt.close();
	    }
	       catch(SQLException ex)
	       {
	          System.out.println("Supplier is not created");
	          throw new Exception ("Supplier is not inserted correct");
	       }
	       return(lol);
	}

	public int updateSupplier(Supplier supplier) 
	{
		Supplier supplierObject  = supplier;
		int lol = -1;

		String query="UPDATE supplier SET "+
						  "Name ='"+ supplierObject.getName()+ "', " +
						  "Address ='"+ supplierObject.getAddress() + "', " +
                          "Zipcode ='"+ supplierObject.getZipCode() + "', " +
                          "Phonenumber ='"+ supplierObject.getPhoneNr() + "' " +
                          "Country = '"+ supplierObject.getCountry() + "'" +
                          "Email = '" + supplierObject.getEmail() + "'" +
                          "WHERE Name = '" + supplierObject.getName() + "'";
                System.out.println("Update query:" + query);
  		try
  		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	lol  = stmt.executeUpdate(query);

	 	 	stmt.close();
		}
	 	catch(Exception ex)
	 	{
	 	 	System.out.println("Update exception in Supplier db: " + ex);
	  	}
		return(lol);
	}

	public int deleteSupplier(String name)
	{
               int lol = -1;
	  
	  	String query="DELETE FROM supplier WHERE Name = '" +
				name + "'";
                System.out.println(query);
	  	try{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	lol  = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}
   	        catch(Exception ex)
   	        {
	 	  	System.out.println("Delete exception in supplier db: "+ex);
   	        }
		return(lol);
	}
}
